/************************************************************************************/
/*  Program name CAPFNDEXT_EMPLOYEE.sql                                             */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 30 NOV, 2005   WONG Wing Kin         First created                               */
/************************************************************************************/
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPFNDEXT_EMPLOYEE';

	lv_Scc_Count	NUMBER	:=	0;
	lv_Err_Count	NUMBER	:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	lv_FileName1	VARCHAR2(100)	:=	'CAPFND_EMPLOYEE.dat';
	lv_FileName2	VARCHAR2(100)	:=	'CAPFND_ADDRESSES.dat';
	lv_PathName		VARCHAR2(100)	:=	'/tmp';
	lv_UTL_File		UTL_FILE.File_Type;

	lv_NGO			VARCHAR2(100);
	lv_NGO_Count	NUMBER;

	CURSOR Cur_F(p_NGO VARCHAR2) IS
	SELECT
		P.*,
		T.User_Person_Type
	FROM
		PER_BUSINESS_GROUPS		B,
		PER_ALL_PEOPLE_F		P,
		PER_PERSON_TYPES		T
	WHERE	SUBSTR(B.Name,4,4)	=	p_NGO
	AND		P.Business_Group_ID	=	B.Business_Group_ID
	AND		P.Person_Type_ID	=	T.Person_Type_ID
	AND		SYSDATE		BETWEEN		P.Effective_Start_Date
						AND			NVL(P.Effective_End_Date,TO_DATE('47121231','YYYYMMDD'))
	ORDER BY	P.Person_ID;

	CURSOR Cur_G(p_NGO VARCHAR2) IS
	SELECT
		A.*
	FROM
		PER_BUSINESS_GROUPS		B,
		PER_ALL_PEOPLE_F		P,
		PER_ADDRESSES			A
	WHERE	SUBSTR(B.Name,4,4)	=	p_NGO
	And		P.Business_Group_ID	=	B.Business_Group_ID
	AND		SYSDATE		BETWEEN		P.Effective_Start_Date
						AND			NVL(P.Effective_End_Date,TO_DATE('47121231','YYYYMMDD'))
	AND		P.Person_ID			=	A.Person_ID
	ORDER BY	P.Person_ID;

BEGIN
	lv_NGO			:=	UPPER('&1');

	SELECT	COUNT('A')
	INTO	lv_NGO_Count
	FROM	HR_ALL_ORGANIZATION_UNITS
	WHERE	SUBSTR(Name,4,4)	=	lv_NGO;

	IF	lv_NGO_Count	>=	1	THEN
		lv_Scc_Count	:=	0;
		lv_Err_Msg		:=	'No Error';
		lv_UTL_File		:=	UTL_FILE.FOpen(lv_PathName, lv_FileName1, 'w');
		FOR C1 IN Cur_F(lv_NGO) LOOP
			UTL_FILE.PUT_LINE(
				lv_UTL_File,
				C1.Person_ID||CHR(9)||
				C1.Effective_Start_Date||CHR(9)||
				C1.Effective_End_Date||CHR(9)||
					C1.Business_Group_ID||CHR(9)||
					C1.Person_Type_ID||CHR(9)||
				C1.Last_Name||CHR(9)||
				C1.Start_Date||CHR(9)||
				C1.Applicant_Number||CHR(9)||
				C1.Background_Check_Status||CHR(9)||
				C1.Background_Date_Check||CHR(9)||
				C1.Blood_Type||CHR(9)||
				C1.Comment_ID||CHR(9)||
				C1.Correspondence_Language||CHR(9)||
				C1.Current_Applicant_Flag||CHR(9)||
				C1.Current_EMP_Or_APL_Flag||CHR(9)||
				C1.Current_Employee_Flag||CHR(9)||
				C1.Date_Employee_Data_Verified||CHR(9)||
				C1.Date_Of_Birth||CHR(9)||
				C1.Email_Address||CHR(9)||
				C1.Employee_Number||CHR(9)||
				C1.Expense_Check_Send_To_Address||CHR(9)||
				C1.Fast_Path_Employee||CHR(9)||
				C1.First_Name||CHR(9)||
				C1.FTE_Capacity||CHR(9)||
				C1.Full_Name||CHR(9)||
				C1.Hold_Applicant_Date_Until||CHR(9)||
				C1.Honors||CHR(9)||
				C1.Internal_Location||CHR(9)||
				C1.Known_As||CHR(9)||
				C1.Last_Medical_Test_By||CHR(9)||
				C1.Last_Medical_Test_Date||CHR(9)||
				C1.Mailstop||CHR(9)||
				C1.Marital_Status||CHR(9)||
				C1.Middle_Names||CHR(9)||
					C1.Nationality||CHR(9)||
				C1.National_Identifier||CHR(9)||
				C1.Office_Number||CHR(9)||
				C1.On_Military_Service||CHR(9)||
				C1.Order_Name||CHR(9)||
				C1.Pre_Name_Adjunct||CHR(9)||
				C1.Previous_Last_Name||CHR(9)||
				C1.Projected_Start_Date||CHR(9)||
				C1.Rehire_Authorizor||CHR(9)||
				C1.Rehire_Reason||CHR(9)||
				C1.Rehire_Recommendation||CHR(9)||
				C1.Resume_Exists||CHR(9)||
				C1.Resume_Last_Updated||CHR(9)||
				C1.Registered_Disabled_Flag||CHR(9)||
				C1.Second_Passport_Exists||CHR(9)||
				C1.Sex||CHR(9)||
				C1.Student_Status||CHR(9)||
				C1.Suffix||CHR(9)||
				C1.Title||CHR(9)||
				C1.Vendor_ID||CHR(9)||
				C1.Work_Schedule||CHR(9)||
				C1.Work_Telephone||CHR(9)||
				C1.Coord_Ben_Med_Pln_No||CHR(9)||
				C1.Coord_Ben_No_Cvg_Flag||CHR(9)||
				C1.Dpdnt_Adoption_Date||CHR(9)||
				C1.Dpdnt_Vlntry_Svce_Flag||CHR(9)||
				C1.Receipt_Of_Death_Cert_Date||CHR(9)||
				C1.Uses_Tobacco_Flag||CHR(9)||
				C1.Benefit_Group_ID||CHR(9)||
				C1.Request_ID||CHR(9)||
				C1.Program_Application_ID||CHR(9)||
				C1.Program_ID||CHR(9)||
				C1.Program_Update_Date||CHR(9)||
				C1.Attribute_Category||CHR(9)||
				C1.Attribute1||CHR(9)||
				C1.Attribute2||CHR(9)||
				C1.Attribute3||CHR(9)||
				C1.Attribute4||CHR(9)||
				C1.Attribute5||CHR(9)||
				C1.Attribute6||CHR(9)||
				C1.Attribute7||CHR(9)||
				C1.Attribute8||CHR(9)||
				C1.Attribute9||CHR(9)||
				C1.Attribute10||CHR(9)||
				C1.Attribute11||CHR(9)||
				C1.Attribute12||CHR(9)||
				C1.Attribute13||CHR(9)||
				C1.Attribute14||CHR(9)||
				C1.Attribute15||CHR(9)||
				C1.Attribute16||CHR(9)||
				C1.Attribute17||CHR(9)||
				C1.Attribute18||CHR(9)||
				C1.Attribute19||CHR(9)||
				C1.Attribute20||CHR(9)||
				C1.Attribute21||CHR(9)||
				C1.Attribute22||CHR(9)||
				C1.Attribute23||CHR(9)||
				C1.Attribute24||CHR(9)||
				C1.Attribute25||CHR(9)||
				C1.Attribute26||CHR(9)||
				C1.Attribute27||CHR(9)||
				C1.Attribute28||CHR(9)||
				C1.Attribute29||CHR(9)||
				C1.Attribute30||CHR(9)||
				C1.Last_Update_Date||CHR(9)||
					C1.Last_Updated_By||CHR(9)||
				C1.Last_Update_Login||CHR(9)||
					C1.Created_By||CHR(9)||
				C1.Creation_Date||CHR(9)||
				C1.Per_Information_Category||CHR(9)||
				C1.Per_Information1||CHR(9)||
				C1.Per_Information2||CHR(9)||
				C1.Per_Information3||CHR(9)||
				C1.Per_Information4||CHR(9)||
				C1.Per_Information5||CHR(9)||
				C1.Per_Information6||CHR(9)||
				C1.Per_Information7||CHR(9)||
				C1.Per_Information8||CHR(9)||
				C1.Per_Information9||CHR(9)||
				C1.Per_Information10||CHR(9)||
				C1.Per_Information11||CHR(9)||
				C1.Per_Information12||CHR(9)||
				C1.Per_Information13||CHR(9)||
				C1.Per_Information14||CHR(9)||
				C1.Per_Information15||CHR(9)||
				C1.Per_Information16||CHR(9)||
				C1.Per_Information17||CHR(9)||
				C1.Per_Information18||CHR(9)||
				C1.Per_Information19||CHR(9)||
				C1.Per_Information20||CHR(9)||
				C1.Per_Information21||CHR(9)||
				C1.Per_Information22||CHR(9)||
				C1.Per_Information23||CHR(9)||
				C1.Per_Information24||CHR(9)||
				C1.Per_Information25||CHR(9)||
				C1.Per_Information26||CHR(9)||
				C1.Per_Information27||CHR(9)||
				C1.Per_Information28||CHR(9)||
				C1.Per_Information29||CHR(9)||
				C1.Per_Information30||CHR(9)||
				C1.Object_Version_Number||CHR(9)||
				C1.Date_Of_Death||CHR(9)||
				C1.Original_Date_Of_Hire||CHR(9)||
				C1.Town_Of_Birth||CHR(9)||
				C1.Region_Of_Birth||CHR(9)||
				C1.Country_Of_Birth||CHR(9)||
				C1.Global_Person_Id||CHR(9)||
				C1.Coord_Ben_Med_PL_Name||CHR(9)||
				C1.Coord_Ben_Med_Insr_Crr_Name||CHR(9)||
				C1.Coord_Ben_Med_Insr_Crr_Ident||CHR(9)||
				C1.Coord_Ben_Med_Ext_Er||CHR(9)||
				C1.Coord_Ben_Med_Cvg_Strt_Dt||CHR(9)||
				C1.Coord_Ben_Med_Cvg_End_Dt||CHR(9)||
				C1.Party_ID||CHR(9)||
				C1.NPW_Number||CHR(9)||
				C1.Current_NPW_Flag||CHR(9)||
				CAPBREXT_COMMON.GetBGName(C1.Business_Group_ID)||CHR(9)||
				C1.User_Person_Type||CHR(9)||
				CAPBREXT_COMMON.GetPerNationality(C1.Nationality)||CHR(9)||
				CAPBREXT_COMMON.GetUser(C1.Last_Updated_By)||CHR(9)||
				CAPBREXT_COMMON.GetUser(C1.Created_By)
			);
			lv_Scc_Count	:=	lv_Scc_Count	+	1;
		END LOOP;
		IF	lv_Scc_Count	=	0	THEN
			lv_Err_Msg	:=	lv_NGO||': No Employee records for this business group';
		END IF;
		UTL_FILE.FClose(lv_UTL_File);
		INSERT INTO CAPCNV_LOG(
			PROG_ID,
			PROCESS_DATE,
			RECORD_PROCESSED,
			RECORD_REJECTED,
			RECORD_EXTRACTED,
			ERR_MSG
		) VALUES (
			lv_Prog_ID||' CAPFND_EMPLOYEE',
			SYSDATE,
			lv_Scc_Count,
			lv_Err_Count,
			lv_Scc_Count-lv_Err_Count,
			lv_Err_Msg
		);
/*********************************************/
		lv_Scc_Count	:=	0;
		lv_Err_Msg		:=	'No Error';
		lv_UTL_File		:=	UTL_FILE.FOpen(lv_PathName, lv_FileName2, 'w');
			FOR C2 IN Cur_G(lv_NGO) LOOP
			UTL_FILE.PUT_LINE(
				lv_UTL_File,
				C2.Address_ID||CHR(9)||
					C2.Business_Group_ID||CHR(9)||
					C2.Person_ID||CHR(9)||		-- Mapping
				C2.Date_From||CHR(9)||
				C2.Primary_Flag||CHR(9)||
				C2.Style||CHR(9)||
				C2.Address_Line1||CHR(9)||
				C2.Address_Line2||CHR(9)||
				C2.Address_Line3||CHR(9)||
				C2.Address_Type||CHR(9)||
				--C2.Comments||CHR(9)||
				C2.Country||CHR(9)||
				C2.Date_To||CHR(9)||
				C2.Postal_Code||CHR(9)||
				C2.Region_1||CHR(9)||
				C2.Region_2||CHR(9)||
				C2.Region_3||CHR(9)||
				C2.Telephone_Number_1||CHR(9)||
				C2.Telephone_Number_2||CHR(9)||
				C2.Telephone_Number_3||CHR(9)||
				C2.Town_Or_City||CHR(9)||
				C2.Request_ID||CHR(9)||
				C2.Program_Application_ID||CHR(9)||
				C2.Program_ID||CHR(9)||
				C2.Program_Update_Date||CHR(9)||
				C2.Addr_Attribute_Category||CHR(9)||
				C2.Addr_Attribute1||CHR(9)||
				C2.Addr_Attribute2||CHR(9)||
				C2.Addr_Attribute3||CHR(9)||
				C2.Addr_Attribute4||CHR(9)||
				C2.Addr_Attribute5||CHR(9)||
				C2.Addr_Attribute6||CHR(9)||
				C2.Addr_Attribute7||CHR(9)||
				C2.Addr_Attribute8||CHR(9)||
				C2.Addr_Attribute9||CHR(9)||
				C2.Addr_Attribute10||CHR(9)||
				C2.Addr_Attribute11||CHR(9)||
				C2.Addr_Attribute12||CHR(9)||
				C2.Addr_Attribute13||CHR(9)||
				C2.Addr_Attribute14||CHR(9)||
				C2.Addr_Attribute15||CHR(9)||
				C2.Addr_Attribute16||CHR(9)||
				C2.Addr_Attribute17||CHR(9)||
				C2.Addr_Attribute18||CHR(9)||
				C2.Addr_Attribute19||CHR(9)||
				C2.Addr_Attribute20||CHR(9)||
				C2.Last_Update_Date||CHR(9)||
					C2.Last_Updated_By||CHR(9)||
				C2.Last_Update_Login||CHR(9)||
					C2.Created_By||CHR(9)||
				C2.Creation_Date||CHR(9)||
				C2.Object_Version_Number||CHR(9)||
				C2.Add_Information13||CHR(9)||
				C2.Add_Information14||CHR(9)||
				C2.Add_Information15||CHR(9)||
				C2.Add_Information16||CHR(9)||
				C2.Add_Information17||CHR(9)||
				C2.Add_Information18||CHR(9)||
				C2.Add_Information19||CHR(9)||
				C2.Add_Information20||CHR(9)||
				C2.Party_ID||CHR(9)||
				C2.Derived_Locale||CHR(9)||
				--C2.GEOMETRY                 MDSYS.SDO_GEOMETRY
				CAPBREXT_COMMON.GetBGName(C2.Business_Group_ID)||CHR(9)||
				CAPBREXT_COMMON.GetUser(C2.Last_Updated_By)||CHR(9)||
				CAPBREXT_COMMON.GetUser(C2.Created_By)
			);
			lv_Scc_Count	:=	lv_Scc_Count	+	1;
		END LOOP;
		IF	lv_Scc_Count	=	0	THEN
			lv_Err_Msg	:=	lv_NGO||': No Employee Address records for this business group';
		END IF;
		UTL_FILE.FClose(lv_UTL_File);
		INSERT INTO CAPCNV_LOG(
			PROG_ID,
			PROCESS_DATE,
			RECORD_PROCESSED,
			RECORD_REJECTED,
			RECORD_EXTRACTED,
			ERR_MSG
		) VALUES (
			lv_Prog_ID||' CAPFND_ADDRESSES',
			SYSDATE,
			lv_Scc_Count,
			lv_Err_Count,
			lv_Scc_Count-lv_Err_Count,
			lv_Err_Msg
		);
	ELSE
		lv_Err_Count	:=	lv_Err_Count + 1;
		lv_Err_Msg		:=	lv_NGO||': No such BG exists';
		INSERT INTO CAPCNV_LOG(
			PROG_ID,
			PROCESS_DATE,
			RECORD_PROCESSED,
			RECORD_REJECTED,
			RECORD_EXTRACTED,
			ERR_MSG
		) VALUES (
			lv_Prog_ID,
			SYSDATE,
			lv_Scc_Count,
			lv_Err_Count,
			lv_Scc_Count-lv_Err_Count,
			lv_Err_Msg
		);
	END IF;

	COMMIT;
END;
/